package net.zjitc.dao;

import net.zjitc.domain.User;
import net.zjitc.utils.C3p0Utils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2017/9/18.
 */
public class UserDao {
    public int pageSize=3;
    public List<User> findPage(int page) throws SQLException {
        Connection conn= C3p0Utils.getConnection();
        List<User> list=new ArrayList<>();
        String sql="select * from user order by id desc limit ?,?";
        PreparedStatement ps=conn.prepareStatement(sql);
        ps.setInt(1,(page-1)*pageSize);
        ps.setInt(2,pageSize);
        ResultSet result=ps.executeQuery();
        while (result.next()){

           User user=new User();
           user.setId(result.getInt(1));
           user.setUsername(result.getString(2));
           user.setPassword(result.getString(3));
           list.add(user);
        }
        return list;
    }
    public List<User> findAllByUtils() throws SQLException {
        //执行SQL的对象                     参数是连接池，是DataSource类或者其子类
        QueryRunner runner=new QueryRunner(C3p0Utils.getDataSource());
        String sql="select*from user";
        //使用封装策略自动封装
        //结果集的封装策略：ResultSetHandle类，
        //因为结果有不同的各类，可能是一个集合，也可能是一个对象
        //所以使用ResultSetHandle类的子类，封装集合的是BeanListHandler，而封闭一个对象的BeanHandler子类
        //User.class是封装的参考模型，javaBean
        List list=(List) runner.query(sql,new BeanListHandler(User.class));
        return list;

    }
    public User findById(int id) throws SQLException {
        QueryRunner runner=new QueryRunner(C3p0Utils.getDataSource());
        String sql="select*from user where id= ?";
        User user=(User)runner.query(sql,new BeanHandler(User.class),id);
        return user;
    }
}
